﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLHelper;
using System.Data.SqlClient;
using ZgOffice.Entities;
using System.Data;

namespace ZgOffice.DAL
{
    public class BaseInfoDAL:CommonDAL
    {
        public bool LoginCheck(LoginType lt)
        {
            SqlParameter[] cmdParams = new SqlParameter[] { 
                    new SqlParameter("@LOGINNAME", SqlDbType.VarChar, 50),
                };

            cmdParams[0].Value = lt.UserName;
            return SQLHelper.SQLHelper.Exists(strERPDBConnection, "SELECT * FROM TABOPERATOR WHERE LOGINNAME = @LOGINNAME ", cmdParams);
        }
        public DataSet ReadSystemListTreeView()
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT DISTINCT A.NAME,B.TABNAME FROM (");
            SQL.Append("SELECT NAME,REPLACE(NAME,'TABDIYTABLE','') AS SNAME FROM SYSOBJECTS WHERE XTYPE = 'U'");
            SQL.Append(") AS A INNER JOIN (");
            SQL.Append("SELECT TABID,TABNAME FROM TABDIYTABLE WHERE BBASE = 0");
            SQL.Append(") AS B ON A.SNAME = LTRIM(STR(B.TABID))");
            return SQLHelper.SQLHelper.ExecuteDataSet(strERPDBConnection, CommandType.Text, SQL.ToString(), null);
        }
        public DataSet ReadDIYTable(string tableName)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("select top 100 * from " + tableName);
            return SQLHelper.SQLHelper.ExecuteDataSet(strERPDBConnection, CommandType.Text, SQL.ToString(), null);
        }
        public DataSet ReadSysListTree()
        {
            DataSet ds = new DataSet();
            ds.Tables.Add("SYS_TABLE");
            ds.Tables["SYS_TABLE"].Columns.Add("ID", Type.GetType("System.String"));
            ds.Tables["SYS_TABLE"].Columns.Add("ParentID", Type.GetType("System.String"));
            ds.Tables["SYS_TABLE"].Columns.Add("sName", Type.GetType("System.String"));
            ds.Tables["SYS_TABLE"].Columns.Add("sCode", Type.GetType("System.String"));
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "01", "", "系统设置", "" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0101", "01", "导航菜单", "tabNavigate" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0102", "01", "单位名称", "tabEnterprise" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0103", "01", "字段名称", "tabDIYTableField" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0104", "01", "部门明细", "tabDepartment" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0105", "01", "数据类型", "tabDataType" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0106", "01", "数据规范-自动编码规则", "tabDataRules3" });//
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0107", "01", "数据规范-窗体条件显示规则", "tabDataRules2" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0108", "01", "数据规范中的固定值内容", "tabDataRules1" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0109", "01", "数据规范目录", "tabDataRuleCategory" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0110", "01", "数据规范", "tabDataRule" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0111", "01", "模板菜单目录", "tabCategory" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0112", "01", "自动编码", "tabAutoBM" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0113", "01", "系统菜单目录", "tabCAdminR" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "02", "", "人员设置", "" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0201", "02", "角色", "tabRole" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0202", "02", "操作员", "tabOperator" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0203", "02", "登陆信息", "tabLoginInfo" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0204", "02", "登陆日志", "tabLoginLog" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "03", "", "单据设置", "" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0301", "03", "操作日志", "tabLog" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0302", "03", "修改权限", "tabQXEdit" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0303", "03", "单据权限", "tabQX" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0304", "03", "单据锁定公式", "tabLockRepDataFormula" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0305", "03", "单据模板-界面字段公式与名称", "tabGridTextOfFormat" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0306", "03", "表达式-系统公式", "tabFunctions" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0307", "03", "表达式-默认分类", "tabFunction" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0308", "03", "扩展函数使用数据表", "tabFormulaWithSSFunction" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0309", "03", "表间公式", "tabFormula" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0310", "03", "表间公式填充规范", "tabFillRule" });
            ds.Tables["SYS_TABLE"].Rows.Add(new object[] { "0311", "03", "外部数据源信息", "tabDataSrcOther" });
            return ds;

        }
        public DataSet ReadDiyTableListTreeView()
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("select * from (select CatID as ID,sName,SJ as ParentID from tabCategory");
            SQL.Append(" union all ");
            SQL.Append("select 10000 + RepID as ID,sName,CatID as ParentID FROM tabReport) as a order by a.ParentID");
            return SQLHelper.SQLHelper.ExecuteDataSet(strERPDBConnection, CommandType.Text, SQL.ToString(), null);
        }
    }
}
